package org.lex.jdbc.util;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.lex.jdbc.meta.ColumnBean;
import org.lex.jdbc.meta.ForeignKeyBean;
import org.lex.jdbc.meta.PrimaryKeyBean;
import org.lex.jdbc.meta.SchemaBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class MetaDataHelper {
	private Logger log = LoggerFactory.getLogger(MetaDataHelper.class);

	public String[] getColumnNames(ResultSetMetaData md) throws SQLException {
		String[] columnNames = new String[md.getColumnCount()];
		for (int i = 0; i < columnNames.length; i++) {
			columnNames[i] = md.getColumnLabel(i + 1);
		}
		return columnNames;
	}

	public List<PrimaryKeyBean> getPrimaryKeys(DatabaseMetaData md, String catalog, String schema, String table) throws SQLException {
		ResultSet rs = md.getPrimaryKeys(catalog, schema, table);
		ResultSetHandler handler = new BeanListHandler(PrimaryKeyBean.class);
		return (List<PrimaryKeyBean>) handler.handle(rs);
	}

	public List<ForeignKeyBean> getImportedForeignKeys(DatabaseMetaData md, String catalog, String schema, String table) throws SQLException {
		ResultSet rs = md.getImportedKeys(catalog, schema, table);
		ResultSetHandler handler = new BeanListHandler(ForeignKeyBean.class);
		return (List<ForeignKeyBean>) handler.handle(rs);
	}

	public List<ColumnBean> getColumns(DatabaseMetaData md, String catalog, String schema, String table, String column) throws SQLException {
		ResultSet rs = md.getColumns(catalog, schema, table, column);
		BeanListHandler h = new BeanListHandler(ColumnBean.class);
		return (List<ColumnBean>) h.handle(rs);
	}

	public String[] getCatalogs(DatabaseMetaData md) throws SQLException {
		ResultSet rs = md.getCatalogs();
		List<Object[]> names = (List<Object[]>) new ArrayListHandler().handle(rs);

		String[] result = null;
		if (null == names || 0 == names.size()) {
			result = new String[] { "" };
		} else {
			result = new String[names.size()];
			int i = 0;
			for (Iterator<Object[]> iter = names.iterator(); iter.hasNext();) {
				result[i++] = String.valueOf(iter.next()[0]);
			}
		}
		log.info("Catalogs: " + Arrays.toString(result));
		return result;
	}

	public List<SchemaBean> getSchemas(DatabaseMetaData md, String catalog, String schemaPattern) throws SQLException {
		ResultSet rs = md.getSchemas(catalog, schemaPattern);
		BeanListHandler handler = new BeanListHandler(SchemaBean.class);
		return (List<SchemaBean>) handler.handle(rs);
	}

	public List<SchemaBean> getSchemas(DatabaseMetaData md) throws SQLException {
		ResultSet rs = md.getSchemas();
		BeanListHandler handler = new BeanListHandler(SchemaBean.class);
		return (List<SchemaBean>) handler.handle(rs);
	}

	public String[] getTableTypes(DatabaseMetaData md) throws SQLException {
		ResultSet rs = md.getTableTypes();
		ColumnListHandler handler = new ColumnListHandler(1);
		List<Object> result = handler.handle(rs);

		String[] types = null;
		if (null == result || 0 == result.size()) {
			types = new String[0];
		} else {
			types = result.toArray(new String[0]);
		}
		log.info("Table Types: " + Arrays.toString(types));
		return types;
	}

	public String[] getTables(DatabaseMetaData md, String catalog, String[] types) throws SQLException {
		ResultSet rs = md.getTables(catalog, null, null, types);
		ColumnListHandler h = new ColumnListHandler(3);
		List<Object> result = h.handle(rs);
		if (null == result) {
			result = Collections.emptyList();
		}
		String[] tables = result.toArray(new String[result.size()]);
		log.info("Tables(" + catalog + ", " + Arrays.toString(types) + "): " + Arrays.toString(tables));
		return tables;
	}

	public void test(DatabaseMetaData md) throws SQLException {
		String catalog = "mysql";
		Object[][] rs = { { "getSchemas", md.getSchemas(catalog, null) },
				//
				{ "schemas", md.getSchemas() },
				//
				{ "getTables", md.getTables(catalog, null, null, null) },
				//
				{ "tableTypes", md.getTableTypes() } };
		for (Object[] r : rs) {
			print((ResultSet) r[1], (String) r[0]);
		}
	}

	private void print(ResultSet rs, String title) throws SQLException {
		System.out.println("=====" + title + "=====");
		MapListHandler handler = new MapListHandler();
		List<Map<String, Object>> list = handler.handle(rs);
		for (Object m : list) {
			System.out.println(m);
		}
		System.out.println();
	}
}
